﻿Imports System.Data.OleDb
Public Class descriptin
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then
            avgBind()
        End If
        Dim sql As String = "INSERT INTO WinePageViews (Wineid,Viewer) VALUES (@f1,@f2)"
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        conn.Open()
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        cmd.ExecuteNonQuery()
        conn.Close()
        cmd.Dispose()
        conn.Dispose()


        Dim sql2 As String = "SELECT * FROM CountWinePageViews WHERE Wineid=@f20"
        Dim conn2 As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        conn2.Open()
        Dim cmd2 As OleDbCommand = New OleDbCommand(sql2, conn2)
        cmd2.Parameters.AddWithValue("@f20", Request.Params("ID"))
        Dim dr2 = cmd2.ExecuteReader()
        dr2.Read()
        If (dr2.HasRows) Then
            lbl_WineViews.Text = dr2("CountViews")
        Else
            lbl_WineViews.Text = 0
        End If
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("connectionString").ConnectionString)
        Dim sql = " INSERT INTO Comments (Author,Subject,Message,wined) VALUES (@f1,@f2,@f3,@f4)"
        Dim cmd = New OleDbCommand(sql, conn)
        conn.Open()
        cmd.Parameters.AddWithValue("@f1", f1.Text)
        cmd.Parameters.AddWithValue("@f2", f2.Text)
        cmd.Parameters.AddWithValue("@f3", f3.Text)
        cmd.Parameters.AddWithValue("@f4", Request.Params("ID"))
        cmd.ExecuteNonQuery()
        conn.Close()
        Response.Redirect("descriptin.aspx?ID=" & Request.Params("ID"))
    End Sub

    Public Sub RatingChanged(ByVal sender As Object, ByVal e As AjaxControlToolkit.RatingEventArgs)
        Dim wID = Request.Params("ID")

        Dim conn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

        Dim SqlString As String


        Dim rmSql = "delete from rate where Rater=@f1 and PhotoFkid=@f2"
        Dim rmCmd As New OleDbCommand(rmSql, conn)
        conn.Open()
        rmCmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        rmCmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
        rmCmd.ExecuteNonQuery()
        conn.Close()

        SqlString = "insert into rate(Rating,PhotoFkid,Rater) values (@f1,@f2,@f3)"
        Dim addCmd As New OleDbCommand(SqlString, conn)
        conn.Open()
        addCmd.Parameters.AddWithValue("@f1", Rating1.CurrentRating)
        addCmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
        addCmd.Parameters.AddWithValue("@f3", User.Identity.Name)
        addCmd.ExecuteNonQuery()
        conn.Close()
        Response.Redirect("descriptin.aspx?ID=" & wID)
    End Sub

    Private Sub avgBind()
        Dim con As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Dim wineID = Request.Params("ID")

        Dim COUNT As Single = 0
        Dim RATING As Single = 0
        Dim Result As Single = 0
        Dim MyCurrentRating As Single = 0

        Dim dataTable As DataTable = New DataTable()
        con.Open()
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT rate.PhotoFkid, AVG(Rating) AS AverageRating FROM(rate) WHERE PhotoFkid=" & wineID & "  GROUP BY PhotoFkid", con)
        Dim DataAdapter As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        DataAdapter.Fill(dataTable)


        If DataTable.Rows.Count <> 0 Then

            RATING = Single.Parse(dataTable.Rows(0)("AverageRating").ToString())

            RATING = CType(Math.Round((RATING), 1), Single)

        Else
            lbRating.Text = 0
        End If
        lbRating.Text = RATING.ToString()

    End Sub


    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button2.Click
        Dim wined = Request.Params("ID")
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("connectionString").ConnectionString)
        conn.Open()

        Dim newtag = txtTag.Text.Split(",")
        For Each newT As String In newtag
            Dim tagpresentCMD As New OleDbCommand("select * from tags where Tag=@f1", conn)
            tagpresentCMD.Parameters.AddWithValue("@f1", newT)
            Dim dr = tagpresentCMD.ExecuteReader()
            If Not dr.HasRows Then
                Dim addTcmd As New OleDbCommand("insert into tags (Tag) values (@f1)", conn)
                addTcmd.Parameters.AddWithValue("@f1", newT)
                addTcmd.ExecuteNonQuery()
            End If
            Dim NTdrCMD As New OleDbCommand("select ID from tags where Tag=@f1", conn)
            NTdrCMD.Parameters.AddWithValue("@f1", newT)
            Dim ntDR = NTdrCMD.ExecuteReader()
            ntDR.Read()
            Dim nID = ntDR.Item("ID")
            Dim makeassociationcmd As New OleDbCommand("insert into Winetagjunction (TagFkid,WineFkid) values (@f1,@f2)", conn)
            makeassociationcmd.Parameters.AddWithValue("@f1", nID)
            makeassociationcmd.Parameters.AddWithValue("@f2", wined)
            makeassociationcmd.ExecuteNonQuery()

        Next

        For Each li As ListItem In CheckBoxList1.Items
            If (li.Selected = True) Then
                Dim sql = "INSERT INTO WinetagJunction(TagFkid,WineFkid) VALUES (@f1,@f2)"
                Dim cmd As New OleDbCommand(sql, conn)
                cmd.Parameters.AddWithValue("@f1", li.Value)
                cmd.Parameters.AddWithValue("@f2", wined)

                cmd.ExecuteNonQuery()
            End If
        Next
        conn.Close()
    End Sub
End Class